﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public partial class LostAndFound
    {
        DbHelperSQL dbhelper = new DbHelperSQL();

        #region 根据信息类型lostproperty,pagenum,ordercondition得到总信息
        /// <summary>
        /// 根据信息类型lostproperty,pagenum,ordercondition得到总信息
        /// </summary>
        /// <param name="lostproperty"></param>
        /// <returns></returns>
        public List<MODEL.LostAndFound> getAll(string lostproperty, int pagenum,string ordercondition)
        {
            //string sql = "select * from LostAndFound laf join Users u on laf.UserId=u.UId where IsDel=0 {0} order by Time desc";

            string sql = "select * from" +
                        "(select row_number()over({1})" +
                        " as snum,* from LostAndFound" +
                        " laf join Users u on laf.UserId=u.UId where " +
                        " {0} IsDel=0)" +
                        " as a " +
                        " {2}";
            sql = string.Format(sql, lostproperty == "" ? "" : " LostProperty=@LostProperty and", "order by " + ordercondition, pagenum == 0 ? "" : "where a.snum>4*(@pagenum-1)and a.snum<=4*@pagenum");
       
            SqlParameter[] paras = { 
                                      new SqlParameter("@LostProperty",lostproperty),
                                      new SqlParameter("@pagenum",pagenum)
                                   };
            DataTable dt = dbhelper.GetDataTable(sql, paras);
            if (dt == null)
            {
                return null;
            }
            else
            {
                List<MODEL.LostAndFound> list = new List<MODEL.LostAndFound>();
                foreach (DataRow dr in dt.Rows)
                {
                    MODEL.LostAndFound model = new MODEL.LostAndFound();
                    setDr2Model(dr, model);
                    list.Add(model);
                }
                return list;
            }
        } 
        #endregion

        #region 修改启事信息
        /// <summary>
        /// 修改启事信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool modifyInfo(MODEL.LostAndFound model)
        {
            string sql = "update LostAndFound set UserId=@UserId,LostProperty=@LostProperty,GoodsName=@GoodsName,Feature=@Feature,Address=@Address,[Time]=@Time,[Image]=@Image where LId=@LId;";
            SqlParameter[] paras = { 
                                       new SqlParameter("@UserId",model.UserId),
                                       new SqlParameter("@LostProperty",model.LostProperty),
                                       new SqlParameter("@GoodsName",model.GoodsName),
                                       new SqlParameter("@Feature",model.Feature),
                                       new SqlParameter("@Address",model.Address),
                                       new SqlParameter("@Time",model.Time),
                                       new SqlParameter("@Image",model.Image),  
                                       new SqlParameter("@LId",model.LId)
                                   };
            int r = dbhelper.ExecuteCommand(sql, paras);
            return r > 0;
        } 
        #endregion

        #region 根据Id号(LId)执行软删除
        /// <summary>
        /// 根据Id号(LId)执行软删除
        /// </summary>
        /// <param name="lid"></param>
        public bool softDelByLId(int lid)
        {
            string sql = "update LostAndFound set IsDel=1 where LId=@LId;";
            SqlParameter[] paras = { 
                                      new SqlParameter("@LId",lid)
                                   };
            int r = dbhelper.ExecuteCommand(sql, paras);
            return r > 0;
        } 
        #endregion

        #region 根据用户Id号查询相关失物招领信息(个人中心)，返回实体对象
        /// <summary>
        /// 根据用户Id号查询相关失物招领信息，返回实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<MODEL.LostAndFound> getModelsByUserId(int userid,int pagenum,string lostproperty,string ordercondition)
        {
            string sql = "select * from"+
                         "(select row_number()over({1})" +
                         " as snum,* from LostAndFound"+
                         " laf join Users u on laf.UserId=u.UId where laf.UserId=@UserId"+
                         " {0} and IsDel=0)"+
                         " as a"+
                         " where a.snum>6*(@pagenum-1)and a.snum<=6*@pagenum";
            sql = string.Format(sql, lostproperty == "总信息" ? "" : "and LostProperty=@lostproperty", "order by " + ordercondition);
            List<MODEL.LostAndFound> list = new List<MODEL.LostAndFound>();
            SqlParameter[] paras = { 
                                       new SqlParameter("@UserId",userid),
                                       new SqlParameter("@pagenum",pagenum),
                                       new SqlParameter("@lostproperty",lostproperty),
                                       //new SqlParameter("@orderCondition",ordercondition)
                                   };
            DataTable dt = dbhelper.GetDataTable(sql, paras);
            if (dt == null)
            {
                return null;
            }
            else
            {
                foreach (DataRow dr in dt.Rows)
                {
                    MODEL.LostAndFound model = new MODEL.LostAndFound();
                    setDr2Model(dr, model);
                    list.Add(model);
                }
                return list;
            }

        }
        #endregion

        #region 根据用户Id号以及分类lostproperty获得总页数
        /// <summary>
        /// 根据用户Id号以及分类lostproperty查询总数
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="lostproperty"></param>
        /// <returns></returns>
        public int getPCByUserId(int userid, string lostproperty)
        {
            string sql = "select count(*) from LostAndFound where {0} IsDel=0 ";
            if (lostproperty != "总信息")
            {
                sql += " and LostProperty=@LostProperty";
            }
            sql = string.Format(sql, userid == 0 ? "" : "UserId=@UserId and");
            SqlParameter[] paras = { 
                                      new SqlParameter("@UserId",userid),
                                      new SqlParameter("@LostProperty",lostproperty)
                                   };
            int r = dbhelper.GetScalar(sql, paras);
            r =(int)Math.Ceiling(r / (4.0));
            return r;
        } 
        #endregion

        #region 根据Id号(LId)查询相关失物招领信息，返回实体对象
        /// <summary>
        /// 根据Id号查询相关失物招领信息，返回实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<MODEL.LostAndFound> getModelById(int id)
        {
            string sql = "select * from LostAndFound laf join Users u on laf.UserId=u.UId and laf.LId=@LId and IsDel=0;";
            List<MODEL.LostAndFound> list = new List<MODEL.LostAndFound>();
            SqlParameter[] paras = { 
                                       new SqlParameter("@LId",id)
                                   };
            DataRow dr = dbhelper.GetDataRow(sql, paras);
            if (dr == null)
            {
                return null;
            }
            else
            {
                MODEL.LostAndFound model = new MODEL.LostAndFound();
                setDr2Model(dr, model);
                list.Add(model);
                return list;
            }
        } 
        #endregion

        #region 发布信息，插入数据
        /// <summary>
        /// 发布信息，插入数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool publishInfo(MODEL.LostAndFound model,out int lid)
        {
            string sql = "insert into LostAndFound(UserId,LostProperty,GoodsName,Feature,Address,[Time],[Image])values(@UserId,@LostProperty,@GoodsName,@Feature,@Address,@Time,@Image);select @@identity;";
            SqlParameter[] paras = { 
                                       new SqlParameter("@UserId",model.UserId),
                                       new SqlParameter("@LostProperty",model.LostProperty),
                                       new SqlParameter("@GoodsName",model.GoodsName),
                                       new SqlParameter("@Feature",model.Feature),
                                       new SqlParameter("@Address",model.Address),
                                       new SqlParameter("@Time",model.Time),
                                       new SqlParameter("@Image",model.Image)                     
                                   };
            //int r = dbhelper.ExecuteCommand(sql, paras);
            lid = dbhelper.GetScalar(sql, paras);
            return lid > 0;
        }
        #endregion

        #region 将数据行转换成实体对象
        /// <summary>
        /// 将数据行转换成实体对象
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="model"></param>
        public void setDr2Model(DataRow dr, MODEL.LostAndFound model)
        {
            if (dr.Table.Columns.Contains("LId") && dr["LId"] != null)
            {
                model.LId = Convert.ToInt32(dr["LId"]);
            }
            if (dr.Table.Columns.Contains("UserId") && !string.IsNullOrEmpty(dr["UserId"].ToString()))
            {
                model.UserId = Convert.ToInt32(dr["UserId"]);
                //外键实体
                MODEL.Users user = new MODEL.Users();
                new DAL.Users().setDr2Model(dr, user);
                model.User = user;
            }
            if (dr.Table.Columns.Contains("LostProperty") && !string.IsNullOrEmpty(dr["LostProperty"].ToString()))
            {
                model.LostProperty = dr["LostProperty"].ToString();
            }
            if (dr.Table.Columns.Contains("GoodsName") && !string.IsNullOrEmpty(dr["GoodsName"].ToString()))
            {
                model.GoodsName = dr["GoodsName"].ToString();
            }
            if (dr.Table.Columns.Contains("Feature") && !string.IsNullOrEmpty(dr["Feature"].ToString()))
            {
                model.Feature = dr["Feature"].ToString();
            }
            if (dr.Table.Columns.Contains("Address") && !string.IsNullOrEmpty(dr["Address"].ToString()))
            {
                model.Address = dr["Address"].ToString();
            }
            if (dr.Table.Columns.Contains("Time") && dr["Time"] != null)
            {
                model.Time = Convert.ToDateTime(dr["Time"]);
            }
            if (dr.Table.Columns.Contains("Image") && !string.IsNullOrEmpty(dr["Image"].ToString()))
            {
                model.Image = dr["Image"].ToString();
            }
            if (dr.Table.Columns.Contains("IsDel") && !string.IsNullOrEmpty(dr["IsDel"].ToString()))
            {
                model.IsDel =Convert.ToBoolean(dr["IsDel"]);
            }

        } 
        #endregion
    }
}
